// 修改ExcelExportUtil.java，支持左侧表头布局
package com.example.dev_demo.excel.exports;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Excel导出工具类
 * 支持动态列、合并表头、多sheet和大数据量导出
 */
public class ExcelExportUtil {
    // 列宽调整系数（根据实际显示效果调整）
    private static final int WIDTH_FACTOR = 300;
    private static final int MAX_COLUMN_WIDTH = 100;
    // 行高调整系数
    private static final short HEIGHT_FACTOR = 20;
    private static final short MAX_ROW_HEIGHT = 500;

    /**
     * 导出Excel文件
     *
     * @param sheets       Sheet列表
     * @param outputStream 输出流
     * @throws IOException IO异常
     */
    public static void exportExcel(List<ExcelSheet> sheets, OutputStream outputStream) throws IOException {
        // 判断是否有大数据量Sheet
        boolean hasLargeDataSheet = sheets.stream().anyMatch(ExcelSheet::isLargeData);

        try (BufferedOutputStream bos = new BufferedOutputStream(outputStream, 8192 * 10);) {

            Workbook workbook;
            if (hasLargeDataSheet) {
                // 如果有大数据量Sheet，使用SXSSFWorkbook
                workbook = new SXSSFWorkbook();
                // 对每个大数据量Sheet设置rowAccessWindowSize
                for (ExcelSheet sheet : sheets) {
                    if (sheet.isLargeData() && sheet.getRowAccessWindowSize() > 0) {
                        ((SXSSFWorkbook) workbook).setCompressTempFiles(true);
                    }
                }
            } else {
                // 否则使用普通的XSSFWorkbook
                workbook = new XSSFWorkbook();
            }

            try {
                // 创建单元格样式
                CellStyle headerStyle = createHeaderStyle(workbook);
                CellStyle dataStyle = createDataStyle(workbook);

                // 为每个Sheet填充数据
                for (ExcelSheet sheet : sheets) {
                    createSheet(workbook, sheet, headerStyle, dataStyle);
                }
                // 写入文件
                workbook.write(bos);
                // 如果是SXSSFWorkbook，清理临时文件
                if (workbook instanceof SXSSFWorkbook) {
                    ((SXSSFWorkbook) workbook).dispose();
                }
            } finally {
                workbook.close();
            }
        }
    }

    public static void exportExcel(List<ExcelSheet> sheets, OutputStream outputStream, HttpServletResponse response, String fileName) throws IOException {
        // 设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION,
                "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
        response.setHeader(HttpHeaders.PRAGMA, "no-cache");
        response.setHeader(HttpHeaders.CACHE_CONTROL, "no-cache");
        response.setDateHeader(HttpHeaders.EXPIRES, 0);
        //导出数据
        exportExcel(sheets, outputStream);
    }

    /**
     * 创建一个Sheet并填充数据
     */
    private static void createSheet(Workbook workbook, ExcelSheet sheet, CellStyle headerStyle, CellStyle dataStyle) {
        // 创建Sheet
        Sheet excelSheet = workbook.createSheet(sheet.getSheetName());

        // 根据布局方向创建表头和填充数据
        if (sheet.getLayoutDirection() == ExcelSheet.LayoutDirection.TOP_HEADER) {
            // 传统布局：表头在顶部
            createTopHeaders(excelSheet, sheet.getHeaders(), sheet.getHeaderCount(), headerStyle);
//            fillTopData(excelSheet, sheet.getHeaders(), sheet.getDataList(), sheet.getHeaderCount(), dataStyle);
        } else {
            // 左侧布局：表头在左侧
            createLeftHeaders(excelSheet, sheet.getHeaders(), sheet.getHeaderCount(), headerStyle);
            fillLeftData(excelSheet, sheet.getHeaders(), sheet.getDataList(), sheet.getHeaderCount(), dataStyle);
        }
    }

    /**
     * 创建左侧表头（表头在左，数据在右）
     */
    private static void createLeftHeaders(Sheet sheet, List<ExcelHeader> headers, int headerColumnCount, CellStyle headerStyle) {
        // 创建表头列
        for (int i = 0; i < headerColumnCount; i++) {
            // 确保行存在
            for (int j = 0; j < headers.stream().mapToInt(ExcelHeader::getRowIndex).max().orElse(0) + 1; j++) {
                if (sheet.getRow(j) == null) {
                    sheet.createRow(j);
                }
            }
        }

        // 填充表头内容并设置合并单元格
        for (ExcelHeader header : headers) {
            Row row = sheet.getRow(header.getRowIndex());
            if (row == null) {
                row = sheet.createRow(header.getRowIndex());
            }
            Cell cell = row.createCell(header.getColumnIndex());
            cell.setCellValue(header.getTitle());
            cell.setCellStyle(headerStyle);

            // 设置行高
            if (header.getHeight() > 0) {
                row.setHeight((short) (header.getHeight() * HEIGHT_FACTOR));
            }

            // 处理合并单元格
            if (header.getColSpan() > 1 || header.getRowSpan() > 1) {
                int lastColumn = header.getColumnIndex() + header.getColSpan() - 1;
                int lastRow = header.getRowIndex() + header.getRowSpan() - 1;
                CellRangeAddress region = new CellRangeAddress(
                        header.getRowIndex(), lastRow,
                        header.getColumnIndex(), lastColumn
                );
                sheet.addMergedRegion(region);
            }
        }
    }

    /**
     * 创建顶部表头（传统布局）
     */
    private static void createTopHeaders(Sheet sheet, List<ExcelHeader> headers, int headerRowCount, CellStyle headerStyle) {
        // 创建表头行
        for (int i = 0; i < headerRowCount; i++) {
            sheet.createRow(i);
        }

        // 填充表头内容并设置合并单元格
        for (ExcelHeader header : headers) {
            Row row = sheet.getRow(header.getRowIndex());
            Cell cell = row.createCell(header.getColumnIndex());
            cell.setCellValue(header.getTitle());
            cell.setCellStyle(headerStyle);

            // 设置列宽
            if (header.getWidth() > 0) {
                sheet.setColumnWidth(header.getColumnIndex(), header.getWidth() * 256);
            }

            // 处理合并单元格
            if (header.getColSpan() > 1 || header.getRowSpan() > 1) {
                int lastColumn = header.getColumnIndex() + header.getColSpan() - 1;
                int lastRow = header.getRowIndex() + header.getRowSpan() - 1;
                CellRangeAddress region = new CellRangeAddress(
                        header.getRowIndex(), lastRow,
                        header.getColumnIndex(), lastColumn
                );
                sheet.addMergedRegion(region);
            }
        }
    }

    /**
     * 填充左侧表头对应的数据（数据在右）
     */
    private static void fillLeftData(Sheet sheet, List<ExcelHeader> headers, List<?> dataList, int headerColumnCount, CellStyle dataStyle) {
        if (dataList == null || dataList.isEmpty()) {
            return;
        }

        // 获取数据字段信息（只取最末列的表头对应的字段）
        List<ExcelHeader> dataHeaders = headers.stream()
                .filter(h -> h.getColumnIndex() == headerColumnCount - 1)
                .sorted((h1, h2) -> h1.getRowIndex() - h2.getRowIndex())
                .collect(Collectors.toList());

        int dataStartColumn = headerColumnCount; // 数据从表头列数之后开始
        int[] maxWidths = new int[dataStartColumn + dataList.size()];

        // 为每条数据创建一列
        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
            int dataColumn = dataStartColumn + dataIndex;
            Object data = dataList.get(dataIndex);

            // 为每个表头行设置对应的数据
            for (ExcelHeader header : dataHeaders) {
                int rowIndex = header.getRowIndex();
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }

                Cell cell = row.createCell(dataColumn);
                cell.setCellStyle(dataStyle);

                // 获取单元格值
                Object value = getFieldValue(data, header.getFieldName());
                setCellValue(cell, value);
            }
        }

        // 调整列宽
        for (int i = 0; i < maxWidths.length; i++) {
            if (maxWidths[i] > 0) {
                int width = Math.min(maxWidths[i] * WIDTH_FACTOR, MAX_COLUMN_WIDTH * 256);
                sheet.setColumnWidth(i, width);
            }
        }
    }

    /**
     * 填充顶部表头对应的数据（传统布局）
     */
    private static void fillTopData(Sheet sheet, List<ExcelHeader> headers, List<?> dataList, int headerRowCount, CellStyle dataStyle) {
        if (dataList == null || dataList.isEmpty()) {
            return;
        }
        final int headerSize = headers.size();
        // 获取数据字段信息（只取最末行的表头对应的字段）
        List<ExcelHeader> dataHeaders = headers.stream()
                .filter(h -> h.getRowIndex() == headerRowCount - 1)
                .sorted((h1, h2) -> h1.getColumnIndex() - h2.getColumnIndex())
                .collect(Collectors.toList());

        int[] maxWidths = new int[headerSize];
        // 填充数据行
        for (int i = 0; i < dataList.size(); i++) {
            int rowNum = headerRowCount + i;
            Row row = sheet.createRow(rowNum);
            Object data = dataList.get(i);

            // 为每一列设置值
            for (ExcelHeader header : dataHeaders) {
                if (header.getFieldName() == null || header.getFieldName().trim().isEmpty()) {
                    continue;
                }

                Cell cell = row.createCell(header.getColumnIndex());
                cell.setCellStyle(dataStyle);

                // 获取单元格值
                Object value = getFieldValue(data, header.getFieldName());
                setCellValue(cell, value);
            }
        }
        // 设置列宽
        for (int i = 0; i < headerSize; i++) {
            if (sheet.getColumnWidth(i) < maxWidths[i] * WIDTH_FACTOR) {
                int width = Math.min(maxWidths[i] * WIDTH_FACTOR, MAX_COLUMN_WIDTH * 256);
                sheet.setColumnWidth(i, width);
            }
        }
    }

    /**
     * 从数据对象中获取字段值
     */
    private static Object getFieldValue(Object data, String fieldName) {
        if (data == null) {
            return null;
        }

        try {
            if (data instanceof Map) {
                // 如果是Map，直接通过key获取值
                return ((Map<?, ?>) data).get(fieldName);
            } else {
                // 如果是JavaBean，通过反射获取字段值
                Field field = data.getClass().getDeclaredField(fieldName);
                field.setAccessible(true);
                return field.get(data);
            }
        } catch (Exception e) {
            // 字段不存在时返回null
            return null;
        }
    }

    /**
     * 设置单元格值，根据值的类型自动转换
     */
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }

        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof java.util.Date) {
            cell.setCellValue((java.util.Date) value);
        } else if (value instanceof java.time.LocalDate) {
            cell.setCellValue(value.toString());
        } else if (value instanceof java.time.LocalDateTime) {
            cell.setCellValue(value.toString());
        } else {
            // 其他类型转换为字符串
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 创建表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 设置背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置字体
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);

        // 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    /**
     * 创建数据单元格样式
     */
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 设置对齐方式
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置自动换行
        style.setWrapText(false);

        return style;
    }
}